package TableAdditions;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Dimension;
import java.awt.EventQueue;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseListener;
import java.awt.event.WindowEvent;
import java.awt.event.WindowListener;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.ListSelectionModel;
import javax.swing.border.EmptyBorder;
import javax.swing.table.DefaultTableModel;

import Utility.Customer;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import javax.swing.JButton;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import javax.swing.JTable;

@SuppressWarnings("serial")
public class SpecialRouteAdditionMenu extends JFrame {

	private JPanel contentPane;
	Boolean customerFlag=false;
	int selectedRow=0;
	String selectedCustomer="",selectedPersonnel="",selectedDate="";
	String selectedCustomerId="";
	String selectedProductId="";
	String selectedPersonelId="";
	String selectedDay="";
	String specialPrice="";
	static int numberOfCustomers=0;
	static Customer[] customers;
	public String routeId="";
	private static int customerCounter=0;
	private JTable customerTable;
	DefaultTableModel model = new DefaultTableModel();
	public static JComboBox customerComboBox;
	private static String selectedValue="";
	public static String specialRouteId="";
	public static String billDate="";
	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					RouteAdditionMenu frame = new RouteAdditionMenu();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	/**
	 * Create the frame.
	 */
	public SpecialRouteAdditionMenu(String personnel,String date) {
		setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
		setBounds(100, 100, 800, 600);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		billDate=date;
		specialRouteId=personnel+"/"+date;
		
		this.addWindowListener(new WindowListener() {
			
			@Override
			public void windowOpened(WindowEvent arg0) {
				// TODO Auto-generated method stub
				
			}
			
			@Override
			public void windowIconified(WindowEvent arg0) {
				// TODO Auto-generated method stub
				
			}
			
			@Override
			public void windowDeiconified(WindowEvent arg0) {
				// TODO Auto-generated method stub
				
			}
			
			@Override
			public void windowDeactivated(WindowEvent arg0) {
				// TODO Auto-generated method stub
				
			}
			
			@Override
			public void windowClosing(WindowEvent arg0) {
				// TODO Auto-generated method stub
				BillAdditionMenu.refreshDestinationsInRoute();
				
			}
			
			@Override
			public void windowClosed(WindowEvent arg0) {
				// TODO Auto-generated method stub
				
			}
			
			@Override
			public void windowActivated(WindowEvent arg0) {
				// TODO Auto-generated method stub
				
			}
		});
		
		//System.out.println("spedicalRouteId= "+specialRouteId);
		
		//System.out.println("Ready to execute sql");
		 	Connection con = null;
	        Statement st = null;
	        ResultSet rs = null;

	        String url = "jdbc:mysql://localhost:3306/mydb";
	        String user = "root";
	        String password = "qweasdzxc";

	        try {
	        	deleteAllRows(model);
	            con = (Connection) DriverManager.getConnection(url, user, password);
	            st = (Statement) con.createStatement();
	            //st.executeUpdate("INSERT INTO job " + "VALUES (1, 'deneme')");
	            //st.executeUpdate("INSERT INTO special_price (special_price_customer_id,special_price_product_id,special_price) " + 
	            	//	"VALUES ('"+cidField.getText()+"'" +",'"+pidField.getText()+"','"+spField.getText()+"')");
	           //st.executeUpdate("ALTER TABLE special_price AUTO_INCREMENT = 1");
	            //st.executeUpdate("DELETE FROM special_price");
	           
	            
	            rs=st.executeQuery("SELECT * FROM special_route WHERE route_id='"+specialRouteId+"'");
	            
	            int specialRouteCounter=0;
	            
	            while(rs.next()){
	            	specialRouteCounter++;
	            }
	            
	            System.out.println("specialRouteCounter="+specialRouteCounter);
	            
	            if(specialRouteCounter==0){
	            	System.out.println("special route adding....");
	            	if(BillAdditionMenu.personnelFlag && BillAdditionMenu.dayFlag){
	            		st.executeUpdate("INSERT INTO special_route (route_id,route_owner_id,route_date)" +
	            				" VALUES ('"+specialRouteId+"','"+BillAdditionMenu.selectedPersonelId+"','"+date+"')");
	            		
	            		System.out.println("destination number to add !!!!!!!!!!! ="+BillAdditionMenu.destinationsInRoute.length);
	            		for(int x=0; x<BillAdditionMenu.destinationsInRoute.length;x++){
	            			st.executeUpdate("INSERT INTO special_route_destination_list (route_id,destination_id)" +
		            				" VALUES ('"+specialRouteId+"','"+BillAdditionMenu.destinationsInRoute[x]+"')");
	            			
	            		}
	            		
	            	}
	            	
	            	
	            }
	           
	           
	            
	            
	            
	            
	            customerComboBox = new JComboBox();
	            customerComboBox.setBounds(50, 77, 138, 20);
	            contentPane.add(customerComboBox);
	            
	            fillCustomerComboBox();
	          
	            
	          
	            customerComboBox.addActionListener(new ActionListener() {
	                public void actionPerformed(ActionEvent event) {
	                    //
	                    // Get the source of the component, which is our combo
	                    // box.
	                    //
	                	Statement stmt = null;
	                	Connection conn = null;
	                	ResultSet rss = null;
	                	String url = "jdbc:mysql://localhost:3306/mydb";
	        	        String user = "root";
	        	        String password = "qweasdzxc";
	        	        
	        	        try {
							conn = (Connection) DriverManager.getConnection(url, user, password);
							stmt = (Statement) conn.createStatement();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
	    	          
	                	
	                	
	                	
	                    JComboBox comboBox = (JComboBox) event.getSource();
	     
	                    //
	                    // Print the selected items and the action command.
	                    //
	                    Object selected = comboBox.getSelectedItem();
	                    //System.out.println("Selected Item  = " + selected);
	                    String command = event.getActionCommand();
	                    //System.out.println("Action Command = " + command);
	                    
	                    selectedCustomer=(String) selected;
	                    try {
							rss=stmt.executeQuery("SELECT * from customer where customer_name='"+selectedCustomer+"'");
							while(rss.next()){
								selectedCustomerId=rss.getString("customer_id");
							}
							//System.out.println("SelectedCustomer id= "+selectedCustomerId);
							conn.close();
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
						}
	            
	                    
	                    selectedCustomer=(String) selected;
	                    
	                    try{
	                    if(selected.equals("Müşteri Adı")){
	                    	customerFlag=false;
	                    }
	                    else{
	                    	customerFlag=true;
	                    }
	                    }catch(NullPointerException e){
	                    	
	                    }
	                    // '"+cidField.getText()+"'
	                    
	                    //
	                    // Detect whether the action command is "comboBoxEdited"
	                    // or "comboBoxChanged"
	                    //
	                    if ("comboBoxEdited".equals(command)) {
	                        ////System.out.println("User has typed a string in " +
	                          //      "the combo box.");
	                    } else if ("comboBoxChanged".equals(command)) {
	                        ////System.out.println("User has selected an item " +
	                          //      "from the combo box.");
	                    }
	                }

					
	            });
	            
	            
	        
	            
	            JButton btnAdd = new JButton("Ekle");
	            btnAdd.addActionListener(new ActionListener() {
	            	public void actionPerformed(ActionEvent arg0) {
	            	}
	            });
	            
	            
	            btnAdd.addMouseListener(new MouseAdapter() {
	            	@Override
	            	public void mouseClicked(MouseEvent arg0) {
	            		
	            		
	            		
	            			//System.out.println("ready to add route");
	            			
	            			Statement stmt = null;
		                	Connection conn = null;
		                	ResultSet rss = null;
		                	String url = "jdbc:mysql://localhost:3306/mydb";
		        	        String user = "root";
		        	        String password = "qweasdzxc";
		        	        
		        	  
		        	        
		        	        try {
		        	            
								conn = (Connection) DriverManager.getConnection(url, user, password);
								stmt = (Statement) conn.createStatement();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
		    	         
							
		                    try {
		                    	
		                    	deleteAllRows(model);
		                    	
		                    	 customerTable.setPreferredScrollableViewportSize(new Dimension(460, 300));
		                    	 customerTable.setFillsViewportHeight(true);
		                    	 
		                    	
		                    	 
		                    	 stmt.executeUpdate("INSERT INTO special_route_destination_list (route_id,destination_id) " + "VALUES ('"+specialRouteId+"'" +
		    			            		",'"+selectedCustomerId+"')");
		                    	 
		                    	
		                    	
		                    	
		                    	rss=stmt.executeQuery("SELECT * from  special_route_destination_list WHERE route_id='"+specialRouteId+"'");
		                    	
		                    	
		                    	while(rss.next()){
		                   		 Object[][] data = new Object[1][1];
		                   		 for(int n=0;n<customers.length;n++){
		                   			 if(rss.getInt("destination_id")==customers[n].getCustomerId()){
		                   				 data[0][0]=customers[n].getCustomerName();
		                   			 }
		                   		 }
		                       	 //System.out.println("destination id = "+rss.getString("destination_id"));
		                   		 model.addRow(data[0]);
		                   	}
		                    	fillRouteList(customerTable, model);
		                    	//customerTable.setModel(model);	
		                    	fillCustomerComboBox();
		                    	conn.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
	                    	
	                    	
	                    	
	                    
					
					
	                
						
	            			
	            		
	            	}
	            });
	            
	            
	            btnAdd.setBounds(211, 76, 89, 23);
	            contentPane.add(btnAdd);
	            
	            JPanel panel = new JPanel();
	            panel.setBounds(68, 147, 430, 401);
	            panel.setBackground(Color.blue);
	            
	            
	            customerTable = new JTable(model);
	            customerTable.setBounds(62, 138, 400, 300);
	           
	            customerTable.setPreferredScrollableViewportSize(new Dimension(420, 300));
	            customerTable.setFillsViewportHeight(true);
	            String header="Müşteri";
	            model.addColumn(header);
	            panel.add(customerTable.getTableHeader());
	           //contentPane.add(customerTable);
	            JScrollPane scrollPane = new JScrollPane(customerTable);
	            customerTable.setFillsViewportHeight(true);
	            panel.setLayout(new BorderLayout());
	            panel.add(customerTable.getTableHeader(),BorderLayout.CENTER);
	            panel.add(scrollPane);
	            //contentPane.add(customerTable, BorderLayout.CENTER);
	            contentPane.add(panel);
	            
	            JButton deleteButton = new JButton("Sil");
	            deleteButton.setBounds(323, 75, 98, 25);
	            contentPane.add(deleteButton);
	            
	            JLabel lblPersonnel = new JLabel(personnel);
	            lblPersonnel.setBounds(130, 14, 170, 15);
	            contentPane.add(lblPersonnel);
	            
	            JLabel lblTarih = new JLabel("Tarih:");
	            lblTarih.setBounds(325, 14, 70, 15);
	            contentPane.add(lblTarih);
	            
	            JLabel lblDate = new JLabel(date);
	            lblDate.setBounds(371, 14, 146, 15);
	            contentPane.add(lblDate);
	            
	            JLabel lblPersonel = new JLabel("Personel:");
	            lblPersonel.setBounds(50, 14, 70, 15);
	            contentPane.add(lblPersonel);
	            
	            
	            
	            
	            
	            customerTable.addMouseListener(new MouseAdapter() {
					
					
					@Override
					public void mouseClicked(MouseEvent arg0) {
						// TODO Auto-generated method stub
						 if (customerTable.getColumnSelectionAllowed()
				                    && !customerTable.getRowSelectionAllowed()) {
							  
				                //System.out.println("11111111111111");
				                // Column selection is enabled
				                // Get the indices of the selected columns
				                int[] vColIndices = customerTable.getSelectedColumns();
				            } else if (!customerTable.getColumnSelectionAllowed()
				                    && customerTable.getRowSelectionAllowed()) {
				            	// //System.out.println("222222222222");
				            	 selectedRow=customerTable.getSelectedRow(); 
				            	 selectedValue=(String) customerTable.getValueAt(selectedRow,0); 
				            	 //System.out.println("selectedValue!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!= "+selectedValue);
				            	 ////System.out.println("selectedCustomer= "+customerNames[selectedRow]);
				                // Row selection is enabled
				                // Get the indices of the selected rows
				                int[] rowIndices = customerTable.getSelectedRows();
				            } else if (customerTable.getCellSelectionEnabled()) {
				                // Individual cell selection is enabled
				            	//System.out.println("333333333333");

				                // In SINGLE_SELECTION mode, the selected cell can be retrieved using
				            	customerTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
				                int rowIndex = customerTable.getSelectedRow();
				                int colIndex = customerTable.getSelectedColumn();
				              

				                // In the other modes, the set of selected cells can be retrieved using
				                customerTable.setSelectionMode(ListSelectionModel.SINGLE_INTERVAL_SELECTION);
				                customerTable.setSelectionMode(ListSelectionModel.MULTIPLE_INTERVAL_SELECTION);

				                // Get the min and max ranges of selected cells
				                int rowIndexStart = customerTable.getSelectedRow();
				                int rowIndexEnd = customerTable.getSelectionModel().getMaxSelectionIndex();
				                int colIndexStart = customerTable.getSelectedColumn();
				                int colIndexEnd = customerTable.getColumnModel().getSelectionModel()
				                    .getMaxSelectionIndex();

				                // Check each cell in the range
				                for (int r=rowIndexStart; r<=rowIndexEnd; r++) {
				                    for (int c=colIndexStart; c<=colIndexEnd; c++) {
				                        if (customerTable.isCellSelected(r, c)) {
				                        	//System.out.println("4444444444444");
				                            // cell is selected
				                        }
				                    }
				                }
				            }
						
					}
				});
		            
	            
	            
	            
	            
	            
	            
	            
	            
	            
	            
	            
	            
	            deleteButton.addMouseListener(new MouseAdapter() {
	            	@Override
	            	public void mouseClicked(MouseEvent arg0) {
	            		
	            			////System.out.println(selectedValue+" will be deleted");
	            			

	            			Statement stmt = null;
		                	Connection conn = null;
		                	ResultSet rss = null;
		                	String url = "jdbc:mysql://localhost:3306/mydb";
		        	        String user = "root";
		        	        String password = "qweasdzxc";
		        	        
		        	  
		        	        
		        	        try {
		        	            
								conn = (Connection) DriverManager.getConnection(url, user, password);
								stmt = (Statement) conn.createStatement();
								int selectedValueId = 0;
								for(int m=0;m<customers.length;m++){
									////System.out.println("get customer name= "+customers[m].getCustomerName()+"  selectedValue= "+selectedValue);
									if(selectedValue.equals(customers[m].getCustomerName())){
										selectedValueId=customers[m].getCustomerId();
										//System.out.println("selectedvalueId!!!!!!!!!!!!!!!!!!!!!!="+selectedValueId);
										break;
									}
								}
								
								
								rss=stmt.executeQuery("SELECT * FROM special_route_destination_list WHERE destination_id='"+selectedValueId+"'");
								
								int duplicateCounter =0;
								
								while(rss.next()){
									
									duplicateCounter++;
								}
								
								//System.out.println("duplicate number= "+duplicateCounter);
								
								
								stmt.executeUpdate("DELETE FROM special_route_destination_list WHERE destination_id='"+selectedValueId+"'");
								
								if(duplicateCounter>1){
									for(int b=0;b<duplicateCounter-1;b++){
										stmt.executeUpdate("INSERT INTO special_route_destination_list (route_id,destination_id)" +
					            				" VALUES ('"+specialRouteId+"','"+selectedValueId+"')");
									}
								}
								
								//stmt.executeUpdate("DELETE FROM route_destination_list WHERE destination_id='"+selectedValueId+"'");
								//stmt.executeUpdate("UPDATE customer set customer_availability='1' WHERE customer_id='"+selectedValueId+"' ");
								fillRouteList(customerTable, model);
								//fillCustomerComboBox();
								conn.close();
							} catch (SQLException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
		    	         
	            			
	            		
	            	}
	            	
	            });
	            
	          
	            //JScrollPane scrollPane = new JScrollPane(customerTable);
                //Add the scroll pane to this panel.
                //add(scrollPane);
	            
	            
	            
	            // getting selected item from table
	            
	           
	            
	           
	            int c=0;
	            
	           
	            fillRouteList(customerTable, model);
	           
	            con.close();
	            

	        } catch (SQLException ex) {
	            Logger lgr = Logger.getLogger(main.class.getName());
	            lgr.log(Level.SEVERE, ex.getMessage(), ex);

	        } finally {
	            try {
	                if (rs != null) {
	                    rs.close();
	                }
	                if (st != null) {
	                    st.close();
	                }
	                if (con != null) {
	                    con.close();
	                }

	            } catch (SQLException ex) {
	                Logger lgr = Logger.getLogger(main.class.getName());
	                lgr.log(Level.WARNING, ex.getMessage(), ex);
	            }
	        }
		
		
	}
	
	
	private static void fillCustomerComboBox() throws SQLException {
		// TODO Auto-generated method stub
		customerComboBox.removeAllItems();
		customerCounter=0;
		numberOfCustomers=0;
		
		Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "qweasdzxc";
        con = (Connection) DriverManager.getConnection(url, user, password);
        st = (Statement) con.createStatement();
		
		
		  rs=st.executeQuery("SELECT * from customer");
          customerComboBox.addItem("Müşteri Adı");
          
          
       
          
          
          while(rs.next())
          {
          	for(int x=0;x<BillAdditionMenu.destinationsInRoute.length;x++){
          	
          		if(BillAdditionMenu.destinationsInRoute[x].equals(rs.getString("customer_id"))){
          			numberOfCustomers++;
          			customerComboBox.addItem(rs.getString("customer_name"));
          		}
          		
          	
          	}
          }
          
          //System.out.println("number of customers= "+numberOfCustomers);
          
          if(numberOfCustomers>0){
        	  
        	  customers=new Customer[numberOfCustomers];
        	  //System.out.println("destinations in route= "+BillAdditionMenu.destinationsInRoute.length);
        	    rs.first();
            	for(int x=0;x<BillAdditionMenu.destinationsInRoute.length;x++){
            		
            		if(BillAdditionMenu.destinationsInRoute[x].equals(rs.getString("customer_id"))){
            			
            			 
            	          customers[customerCounter]=new Customer();            	         
            	          customers[customerCounter].setCustomerName(rs.getString("customer_name"));
            	          customers[customerCounter].setCustomerId(rs.getInt("customer_id"));
            	          //System.out.println("Number of customers= "+numberOfCustomers);
            	          customerCounter++;
            			
            			
            		}
            		
            	
            	
            	}
          
            		
         
          while(rs.next())
          {
        	  
        	  for(int x=0;x<BillAdditionMenu.destinationsInRoute.length;x++){
          		
          		if(BillAdditionMenu.destinationsInRoute[x].equals(rs.getString("customer_id"))){ 
          			////System.out.println("Customer name is added to array");
          			customers[customerCounter]=new Customer();
          			customers[customerCounter].setCustomerName(rs.getString("customer_name"));
          			customers[customerCounter].setCustomerId(rs.getInt("customer_id"));
          			customerCounter++;
          			
          		}
        	  }
        	  
          }
          
          }
          
          
          
	
          
          try{
          for(int i=0;i<customerCounter;i++){
          //	//System.out.println("customer= "+customerNames[i]);
          }
          }catch(NullPointerException e){
          	
          }
          
          
		
	}

	public static void deleteAllRows(final DefaultTableModel model) {
	    for( int i = model.getRowCount() - 1; i >= 0; i-- ) {
	        model.removeRow(i);
	    }
	}
	
	private static void fillRouteList(JTable customerTable,DefaultTableModel model) throws SQLException{
		
		
		
	 	Statement stmt = null;
    	Connection conn = null;
    	ResultSet rss = null;
    	String url = "jdbc:mysql://localhost:3306/mydb";
        String user = "root";
        String password = "qweasdzxc";
        
        
        try {
			conn = (Connection) DriverManager.getConnection(url, user, password);
			stmt = (Statement) conn.createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
     
		
        try {
        	deleteAllRows(model);
        	rss=stmt.executeQuery("SELECT * from  special_route WHERE route_id='"+specialRouteId+"'");
        	//stmt.executeUpdate("ALTER TABLE route_destination_list AUTO_INCREMENT = 1");
            //st.executeUpdate("DELETE FROM special_price");
        	
        	 customerTable.setPreferredScrollableViewportSize(new Dimension(460, 300));
        	 customerTable.setFillsViewportHeight(true);
        	 
        	
        	 
        	 int rowCounter=0;
        	 while(rss.next()){
        		 String route_id=rss.getString("route_id");
        		 //System.out.println("route_id= "+route_id);
        		 rowCounter++;
        	 }
    
        	
        	 
        	if(rowCounter==0){
        		stmt.executeUpdate("INSERT INTO special_route (route_id,route_owner_id,route_date) " + "VALUES ('"+specialRouteId+"'" +
	            		",'"+BillAdditionMenu.selectedPersonelId+"','"+billDate+"')");
        	}
        	
        	
        	
        	rss=stmt.executeQuery("SELECT * from  special_route_destination_list WHERE route_id='"+specialRouteId+"' ORDER BY destination_id");
        	
        	
        	while(rss.next()){
        		 Object[][] data = new Object[1][1];
        		 for(int n=0;n<customers.length;n++){
        			 if(rss.getInt("destination_id")==customers[n].getCustomerId()){
        				 data[0][0]=customers[n].getCustomerName();
        			 }
        		 }
            	 //System.out.println("destination id = "+rss.getString("destination_id"));
        		 model.addRow(data[0]);
        	}
        	
       //customerTable.setModel(model);
        	
        	
        	conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	
    	
		
	}
	
	
	}

